<?php
defined( '_JEXEC' );
//echo '<div class="componentheading">Statzone</div>';

jimport('joomla.application.helper');
require_once( JApplicationHelper::getPath( 'html' ) );
JTable::addIncludePath(JPATH_ADMINISTRATOR.DS.
'components'.DS.$option.DS.'tables');
switch($task)
{
case 'viewPlayers':
showPublishedPlayers($option);
break;
case 'viewPlayer':
showPublishedPlayer($option);
break;
case 'viewClubs':
showPublishedClubs($option);
break;
case 'viewScorecards':
showPublishedScorecards($option);
break;
case 'viewTeams':
showPublishedTeams($option);
break;
case 'viewTeamPlayerStats':
showPublishedTeamPlayerStats($option);
break;
case 'viewTeamPlayers':
showPublishedTeamPlayers($option);
break;
case 'viewTeamGames':
showPublishedTeamGames($option);
break;
case 'viewTeamStats':
showPublishedTeamStats($option);
break;
case 'viewScorecard':
    showScorecard($option);   
break;
default:
showStatzoneSections($option);
break;
}

function showScorecard($option)
{

    $id = JRequest::getVar('id', 0);

    $db =& JFactory::getDBO();
    
    $statsquery = 'SELECT scorecard.id as id, scorecard.division, scorecard.season, scorecard.teamidhome as hometeamid, scorecard.teamidopponent as opponentteamid, hometeam.name as hometeamname, awayteam.name as awayteamname,
                scorecard.firstinndate as firstinndate, scorecard.secondinndate as secondinndate, scorecard.innings as inn, scorecard.result as result, scorecard.resultdetails, scorecard.published,
                scorecard.firstinnscore_home,scorecard.firstinnscore_opponent, scorecard.secondinnscore_home, scorecard.secondinnscore_opponent 
                FROM #__statzone_scorecards AS scorecard
                INNER JOIN #__statzone_teams AS hometeam ON hometeam.id = scorecard.teamidhome
                INNER JOIN #__statzone_teams AS awayteam ON awayteam.id = scorecard.teamidopponent 
                INNER JOIN #__statzone_clubs AS homeclub ON homeclub.id = hometeam.clubid
                WHERE scorecard.id = '. $id
    .$where;
    
    $db->setQuery( $statsquery);
    
    $row = $db->loadObjectList();
    if ($db->getErrorNum())
    {
        echo $db->stderr();
        return false;
    }
    
    //Get the stats for the selected scorecard
    $statrow = $row[0];
	
    $db =& JFactory::getDBO();
    $query = 'SELECT DISTINCT player.id AS playerid, player.firstname AS firstname, player.surname AS surname '
                . 'FROM #__statzone_scorecards as scorecard '
                . 'LEFT JOIN #__statzone_battingscores as bat ON bat.scorecardid = scorecard.id '
                . 'LEFT JOIN #__statzone_players as player ON player.id = bat.playerid '
                . 'WHERE scorecard.id = ' . $id
                . ' UNION '
                . 'SELECT DISTINCT player.id AS playerid, player.firstname AS firstname, player.surname AS surname '
                . 'FROM #__statzone_scorecards as scorecard '
                . 'LEFT JOIN #__statzone_bowlingscores as bowl ON bowl.scorecardid = scorecard.id '
                . 'LEFT JOIN #__statzone_players as player ON player.id = bowl.playerid '
                . 'WHERE scorecard.id = ' . $id
                . ' ORDER BY surname'
                ;
    $db->setQuery( $query );
    $playerlist = $db->loadObjectList();
    if ($db->getErrorNum())
    {
        echo $db->stderr();
        return false;
    }

    //foreach of these players create a row, 
    foreach($playerlist as $playerrow) //fetch the batting and bowling stats
    {
        $playerid = $playerrow->playerid;
        if($playerid > 0 )
        {
            $playeridsstatsmain = $playeridsstatsmain . $playerid . ',';
            $sql = 'SELECT DISTINCT bat1.runs, bat2.runs, bat1.notout, bat2.notout, '
            . ' bowl1.overs , bowl1.maidens , bowl1.runs, bowl1.wickets, '
            . ' bowl2.overs , bowl2.maidens , bowl2.runs, bowl2.wickets, ' 
            . ' bat1.id as bat1id, bat2.id as bat2id, bowl1.id as bowl1id, bowl2.id as bowl2id '
            . ' FROM #__statzone_scorecards as scorecard '
            . ' LEFT JOIN #__statzone_battingscores as bat1 ON bat1.scorecardid = scorecard.id AND bat1.inningsno = 1 AND bat1.playerid =' . $playerid
            . ' LEFT JOIN #__statzone_bowlingscores as bowl1 ON bowl1.scorecardid = scorecard.id AND bowl1.inningsno = 1 AND bowl1.playerid =' . $playerid
            . ' LEFT JOIN #__statzone_battingscores as bat2 ON bat2.scorecardid = scorecard.id AND bat2.inningsno = 2 AND bat2.playerid =' . $playerid
            . ' LEFT JOIN #__statzone_bowlingscores as bowl2 ON bowl2.scorecardid = scorecard.id AND bowl2.inningsno = 2 AND bowl2.playerid =' . $playerid
            . ' WHERE scorecard.id = ' . $id
            //. ' AND bat1.playerid =' . $playerid
            //. ' AND bowl1.playerid =' . $playerid
            //. ' AND bat2.playerid =' . $playerid
            //. ' AND bowl2.playerid =' . $playerid
            ;
            
            $db->setQuery($sql);
            if (!$db->query())
            {
                
                return JError::raiseWarning( 500, $db->getErrorMsg() );
            }
            $playerstats = $db->loadRow() ;
            
            $playername = $playerrow->surname .', '.$playerrow->firstname;
            //echo '<br>SCid='.$row->id.' $playername:' . $playername . ' 1st inn run= ' . $playerstats[0];
            //get the rest of the stats
            
            $inn1NotOut = $playerstats[2] ==1 ? '*' : '';
            $inn2NotOut = $playerstats[3] ==1 ? '*' : '';
            //if this is an existing scorecard then show the results
            //else just show empty values
            $innings1row = $innings1row . ''
                .'<tr>'
                .'<td nowrap=\'true\'>'.$playername .' ['.$playerid.']</td>'
                .'<td>'.$playerstats[0]. $inn1NotOut .'</td>'
                .'<td>'. $playerstats[1]. $inn2NotOut . '</td>'
                .'<td>'.$playerstats[4].'</td>'
                .'<td>'.$playerstats[5].'</td>'
                .'<td>'.$playerstats[6].'</td>'
                .'<td>'.$playerstats[7].'</td>'
                .'<td>'.$playerstats[8].'</td>'
                .'<td>'.$playerstats[9].'</td>'
                .'<td>'.$playerstats[10].'</td>'
                .'<td>'.$playerstats[11].'</td>'
                .'</tr>';
        }
        
    }//ends for

    if($innings1row  > '') //might be an empty stats sheet
    {
        $statstable = getStatzTable($innings1row);
    }
     
	HTML_statzone::showScorecard($statrow, $statstable, $option);
}

function showStatzoneSections($option)
{
$db =& JFactory::getDBO();
$query = "SELECT * FROM #__menu WHERE
parent = '55'";// [SH] Theres no need to get the list of items from the menu? Just go straight to the html and hard code the menu? what seems to be the prob?
$db->setQuery( $query );
$rows = $db->loadObjectList();
if ($db->getErrorNum())
{
echo $db->stderr();
return false;
}
	HTML_statzone::showSections($rows, $option);
}

function showPublishedClubs($option)
{
    $db =& JFactory::getDBO();
    $context			= 'com_statzone.club.list.';
    global $mainframe;
    
    $filter_provid		= $mainframe->getUserStateFromRequest( $context.'filter_provid',		'filter_provid',		'',			'int' );
    $search				= $mainframe->getUserStateFromRequest( $context.'search',			'search',			'',			'string' );
    
    $limit		= $mainframe->getUserStateFromRequest( 'global.list.limit', 'limit', $mainframe->getCfg('list_limit'), 'int' );
    $limitstart = JRequest::getVar('limitstart',0);//$mainframe->getUserStateFromRequest( $context.'limitstart', 'limitstart', 0, 'int' ); // Bug in core JPagination doesnt allow Start or 1 to work on the page list - see http://forum.joomla.org/viewtopic.php?f=199&t=268658 and http://docs.joomla.org/Talk:Using_JPagination_in_your_component
    
    $where = array();
            
    if ($filter_provid) {
        $where[] = 'clubs.provinceid = ' . (int) $filter_provid;
    }
       
    if ($search) {
        $where[] = 'LOWER(con.title) LIKE '.$db->Quote( '%'.$db->getEscaped( $search, true ).'%', false );
    }
    
    $where		= count( $where ) ? ' AND ' . implode( ' AND ', $where ) : '';
    
    $query = "SELECT COUNT(*)
    FROM #__content con 
    INNER JOIN #__statzone_clubs AS clubs ON clubs.contentid = con.id
    INNER JOIN #__statzone_province AS prov ON prov.id = clubs.provinceid
    WHERE showClub = '1'"
    .$where;
        
    $db->setQuery( $query);
    $total = $db->loadResult();
    
    $query = "SELECT clubs.id,clubs.contentid, con.title, prov.name 
    FROM #__content con 
    INNER JOIN #__statzone_clubs AS clubs ON clubs.contentid = con.id
    INNER JOIN #__statzone_province AS prov ON prov.id = clubs.provinceid
    WHERE showClub = '1'"
    .$where;
    
    $query = $query . " ORDER BY prov.id, con.title";
    
    jimport('joomla.html.pagination');
    $pageNav = new JPagination( $total, $limitstart, $limit );
    
    $db->setQuery( $query, $pageNav->limitstart, $pageNav->limit );
    
    $rows = $db->loadObjectList();
    if ($db->getErrorNum())
    {
        echo $db->stderr();
        return false;
    }
    
    
    // ________build list of provinces for filtering________
    $javascript		= 'onchange="this.form.submit();"';
    
    $sql = 'SELECT id as provinceid, name'
    . ' FROM #__statzone_province'
    ;
    $db->setQuery($sql);
    if (!$db->query())
    {
        $this->setRedirect( 'index.php?option=com_statzone' );
        return JError::raiseWarning( 500, $db->getErrorMsg() );
    }

    $provincelist[]		= JHTML::_('select.option',  '0', JText::_( '- Select Province -' ), 'provinceid', 'name' );
    $provincelist			= array_merge( $provincelist, $db->loadObjectList() );
    $lists['filter_provid']		= JHTML::_('select.genericlist',   $provincelist, 'filter_provid', 'class="inputbox" size="1"'.$javascript,'provinceid', 'name', $filter_provid );

    // search filter
	$lists['search']= $search;
    
	HTML_statzone::showClubs($rows, $option,$pageNav,$lists);
}

function showPublishedTeams($option)
{
$teamName = JRequest::getVar('teamName', 0);
$id = JRequest::getVar('id', 0);
$db =& JFactory::getDBO();
$query = "select id,clubid,name 
from #__statzone_teams teams
where clubid =". $id;
$db->setQuery( $query );
$rows = $db->loadObjectList();
if ($db->getErrorNum())
{
echo $db->stderr();
return false;
}
	HTML_statzone::showTeams($rows, $option);
}


function showPublishedScorecards($option)
{
    $db =& JFactory::getDBO();
    $context			= 'com_statzone.club.list.';
    global $mainframe;
    
    $filter_provid		= $mainframe->getUserStateFromRequest( $context.'filter_provid',		'filter_provid',		'',			'int' );
    $filter_division		= $mainframe->getUserStateFromRequest( $context.'filter_division',		'filter_division',		'',			'string' );
    $search				= $mainframe->getUserStateFromRequest( $context.'search',			'search',			'',			'string' );
    
    $limit		= $mainframe->getUserStateFromRequest( 'global.list.limit', 'limit', $mainframe->getCfg('list_limit'), 'int' );
    $limitstart = JRequest::getVar('limitstart',0);//$mainframe->getUserStateFromRequest( $context.'limitstart', 'limitstart', 0, 'int' );
    
    $where = array();
            
    if ($filter_provid) {
        $where[] = 'homeclub.provinceid = ' . (int) $filter_provid;
    }
    if ($filter_division) {
        $where[] = 'LOWER(scorecard.division) LIKE '.$db->Quote( '%'.$db->getEscaped( $filter_division, true ).'%', false );
    }
       
    if ($search) {
        $where[] = 'LOWER(hometeam.name) LIKE '.$db->Quote( '%'.$db->getEscaped( $search, true ).'%', false )
                   . ' OR LOWER(awayteam.name) LIKE '.$db->Quote( '%'.$db->getEscaped( $search, true ).'%', false )
                   . ' OR LOWER(scorecard.season) LIKE '.$db->Quote( '%'.$db->getEscaped( $search, true ).'%', false );
    }
    
    $where		= count( $where ) ? ' AND ' . implode( ' AND ', $where ) : '';
    
    $query = 'SELECT COUNT(*) 
                FROM #__statzone_scorecards AS scorecard
                INNER JOIN #__statzone_teams AS hometeam ON hometeam.id = scorecard.teamidhome
                INNER JOIN #__statzone_teams AS awayteam ON awayteam.id = scorecard.teamidopponent 
                INNER JOIN #__statzone_clubs AS homeclub ON homeclub.id = hometeam.clubid
                WHERE scorecard.published = 1'
                .$where;
        
    $db->setQuery( $query);
    $total = $db->loadResult();
    
    $query = "SELECT scorecard.id as id, scorecard.teamidhome as hometeamid, scorecard.teamidopponent as opponentteamid, hometeam.name as hometeamname, awayteam.name as awayteamname,
    scorecard.firstinndate as firstinndate, scorecard.secondinndate as secondinndate, scorecard.innings as inn, scorecard.result as result, scorecard.resultdetails, scorecard.published
    FROM #__statzone_scorecards AS scorecard
    INNER JOIN #__statzone_teams AS hometeam ON hometeam.id = scorecard.teamidhome
    INNER JOIN #__statzone_teams AS awayteam ON awayteam.id = scorecard.teamidopponent
    INNER JOIN #__statzone_clubs AS homeclub ON homeclub.id = hometeam.clubid
    WHERE scorecard.published = 1 "
    .$where;
    $query = $query . " ORDER BY scorecard.firstinndate DESC, scorecard.division, hometeam.name";
    
    jimport('joomla.html.pagination');
    $pageNav = new JPagination( $total, $limitstart, $limit );
    
    $db->setQuery( $query, $pageNav->limitstart, $pageNav->limit );
    
    $rows = $db->loadObjectList();
    if ($db->getErrorNum())
    {
        echo $db->stderr();
        return false;
    }
    
     // ________build list of provinces and divisions for filtering________
    $javascript		= 'onchange="this.form.submit();"';
    
    $sql = 'SELECT id as provinceid, name'
    . ' FROM #__statzone_province'
    ;
    $db->setQuery($sql);
    if (!$db->query())
    {
        $this->setRedirect( 'index.php?option=com_statzone' );
        return JError::raiseWarning( 500, $db->getErrorMsg() );
    }

    $provincelist[]		= JHTML::_('select.option',  '0', JText::_( '- Select Province -' ), 'provinceid', 'name' );
    $provincelist			= array_merge( $provincelist, $db->loadObjectList() );
    $lists['filter_provid']		= JHTML::_('select.genericlist',   $provincelist, 'filter_provid', 'class="inputbox" size="1"'.$javascript,'provinceid', 'name', $filter_provid );

    $divisionlist[]		= JHTML::_('select.option',  '0', JText::_( '- Select Division -' ), 'divisionid', 'division' );
    $divisionlist[]     = JHTML::_('select.option',  '1', JText::_( '1A' ), 'divisionid', 'division' );
    $divisionlist[]     = JHTML::_('select.option',  '2', JText::_( '2A' ), 'divisionid', 'division' );
    $divisionlist[]     = JHTML::_('select.option',  '3', JText::_( '3A' ), 'divisionid', 'division' );
    $divisionlist[]     = JHTML::_('select.option',  '4', JText::_( '2A' ), 'divisionid', 'division' );
    $divisionlist[]     = JHTML::_('select.option',  '5', JText::_( '2B' ), 'divisionid', 'division' );
    $divisionlist[]     = JHTML::_('select.option',  '6', JText::_( '2C' ), 'divisionid', 'division' );
    $divisionlist[]     = JHTML::_('select.option',  '7', JText::_( '3A' ), 'divisionid', 'division' );
    $divisionlist[]     = JHTML::_('select.option',  '8', JText::_( '3B' ), 'divisionid', 'division' );
    $divisionlist[]     = JHTML::_('select.option',  '9', JText::_( '3C' ), 'divisionid', 'division' );
    
    $lists['filter_division']		= JHTML::_('select.genericlist',   $divisionlist, 'filter_division', 'class="inputbox" size="1"'.$javascript,'divisionid', 'division', $filter_division );
    // search filter
	$lists['search']= $search;
    
	HTML_statzone::showScorecards($rows, $option,$lists,$pageNav);
}
function showPublishedPlayers($option)
{
    global $mainframe;
    $db =& JFactory::getDBO();
    $context			= 'com_statzone.player.list.';
    
    $filter_provid		= $mainframe->getUserStateFromRequest( $context.'filter_provid',		'filter_provid',		'',			'int' );
    $filter_clubid		= $mainframe->getUserStateFromRequest( $context.'filter_clubid',		'filter_clubid',		'',			'int' );
    $filter_teamid		= $mainframe->getUserStateFromRequest( $context.'filter_teamid',		'filter_teamid',		'',			'int' );
    $search				= $mainframe->getUserStateFromRequest( $context.'search',			'search',			'',			'string' );

    $limit		= $mainframe->getUserStateFromRequest( 'global.list.limit', 'limit', $mainframe->getCfg('list_limit'), 'int' );
    $limitstart = JRequest::getVar('limitstart',0);//$mainframe->getUserStateFromRequest( $context.'limitstart', 'limitstart', 0, 'int' );
    
    $where = array();
        
    if ($filter_provid) {
        $where[] = 'clubs.provinceid = ' . (int) $filter_provid;
    }
    if ($filter_clubid) {
        $where[] = 'players.clubid = ' . (int) $filter_clubid;
    }
    if ($filter_teamid) {
        $where[] = 'pt.teamid = ' . (int) $filter_teamid;
    }
    
    if ($search) {
        $where[] = 'LOWER(players.firstname) LIKE '.$db->Quote( '%'.$db->getEscaped( $search, true ).'%', false )
                   .' OR LOWER(players.surname) LIKE '.$db->Quote( '%'.$db->getEscaped( $search, true ).'%', false );
    }

    $where		= count( $where ) ? ' AND ' . implode( ' AND ', $where ) : '';
    
    $query = "SELECT COUNT(*)
        FROM #__statzone_players players 
        INNER JOIN #__statzone_clubs clubs ON clubs.id = players.clubid 
        INNER JOIN #__content content ON clubs.contentid = content.id
        INNER JOIN #__statzone_province prov ON prov.id = clubs.provinceid
        WHERE
        showplayer = '1' "
        . $where 
        ;
        
    $db->setQuery( $query);
    $total = $db->loadResult();
    
    $query = "SELECT DISTINCT players.id,firstname,surname,clubs.id as clubid, content.title as club,players.contentid as contentid, prov.name province
        FROM #__statzone_players players 
        INNER JOIN #__statzone_clubs clubs ON clubs.id = players.clubid 
        INNER JOIN #__content content ON clubs.contentid = content.id
        INNER JOIN #__statzone_province prov ON prov.id = clubs.provinceid
        LEFT JOIN #__statzone_playerteams pt ON pt.playerid = players.id
        WHERE
        showplayer = '1' "
        . $where 
        ."
        ORDER BY surname, firstname";
        
      
    jimport('joomla.html.pagination');
    $pageNav = new JPagination( $total, $limitstart, $limit );
    
    $db->setQuery( $query, $pageNav->limitstart, $pageNav->limit );
    $rows = $db->loadObjectList();
    if ($db->getErrorNum())
    {
        echo $db->stderr();
        return false;
    }
    
    //build some lists for the filtering
    $javascript		= 'onchange="this.form.submit();"';
    // ________build list of provinces ________
    $sql = 'SELECT id as provinceid, name'
    . ' FROM #__statzone_province'
    ;
    $db->setQuery($sql);
    if (!$db->query())
    {
        $this->setRedirect( 'index.php?option=com_statzone' );
        return JError::raiseWarning( 500, $db->getErrorMsg() );
    }

    $provincelist[]		= JHTML::_('select.option',  '0', JText::_( '- Select Province -' ), 'provinceid', 'name' );
    $provincelist			= array_merge( $provincelist, $db->loadObjectList() );
    $lists['filter_provid']		= JHTML::_('select.genericlist',   $provincelist, 'filter_provid', 'class="inputbox" size="1"'.$javascript,'provinceid', 'name', $filter_provid );

    // ________build list of clubs________
    $sql = 'SELECT c.id as clubid, content.title AS name'
    . ' FROM #__statzone_clubs AS c'
    . ' INNER JOIN #__content AS content ON c.contentid = content.id'
    . ' INNER JOIN #__statzone_province AS provinces ON c.provinceid = provinces.id'
    ;
    if ($filter_provid) 
    {
        $sql = $sql . " WHERE provinces.id = " . $filter_provid;
    }
    $sql = $sql . ' ORDER BY content.title';
    
    $db->setQuery($sql);
    if (!$db->query())
    {
        $this->setRedirect( 'index.php?option=com_statzone' );
        return JError::raiseWarning( 500, $db->getErrorMsg() );
    }
    $clublist[]		= JHTML::_('select.option',  '0', JText::_( '- Select Club -' ), 'clubid', 'name' );
    $clublist			= array_merge( $clublist, $db->loadObjectList() );
    $lists['filter_clubid']		= JHTML::_('select.genericlist',   $clublist, 'filter_clubid', 'class="inputbox" size="1"' . $javascript,'clubid', 'name', $filter_clubid );

    // ________build list of teams________
    $sql = 'SELECT t.id as teamid, t.name AS teamname'
    . ' FROM #__statzone_teams AS t'
    . ' INNER JOIN #__statzone_clubs AS c ON c.id = t.clubid'
    . ' INNER JOIN #__statzone_province AS provinces ON c.provinceid = provinces.id'
    ;
    if ($filter_clubid) 
    {
        $sql = $sql . " WHERE c.id = " . $filter_clubid;
    }
    $sql = $sql . ' ORDER BY t.name';
    $db->setQuery($sql);
    if (!$db->query())
    {
        $this->setRedirect( 'index.php?option=com_statzone' );
        return JError::raiseWarning( 500, $db->getErrorMsg() );
    }
    $teamlist[]		= JHTML::_('select.option',  '0', JText::_( '- Select Team -' ), 'teamid', 'teamname' );
    $teamlist			= array_merge( $teamlist, $db->loadObjectList() );
    $lists['filter_teamid']		= JHTML::_('select.genericlist',   $teamlist, 'filter_teamid', 'class="inputbox" size="1"'. $javascript,'teamid', 'teamname', $filter_teamid );

    // search filter
	$lists['search']= $search;
    
	HTML_statzone::showPlayers($rows, $option,$pageNav, $lists);
}

function showPublishedTeamPlayers($option)
{
    $teamName = JRequest::getVar('teamName', 0);
    $id = JRequest::getVar('id', 0);
    $db =& JFactory::getDBO();
    $query = "select players.firstname, players.contentid, players.surname
				from #__statzone_players players
				where players.id in (
										select distinct playerid from (
										(
										select playerid from #__statzone_battingscores
										where scorecardid = (
										select id as latest_scorecardid from 
										#__statzone_scorecards
										where teamidhome = " . $id . " and firstinndate in (select max(firstinndate) from #__statzone_scorecards where teamidhome = " . $id . ") limit 1)
										)
										union 
										(
										select playerid from #__statzone_bowlingscores
										where scorecardid = (
										select id as latest_scorecardid from 
										#__statzone_scorecards
										where teamidhome = " . $id . " and firstinndate in (select max(firstinndate) from #__statzone_scorecards where teamidhome = " . $id . ") limit 1)

										)
										) as playerslist
				)
				ORDER BY players.surname";
    $db->setQuery( $query );
    $rows = $db->loadObjectList();
    if ($db->getErrorNum())
    {
        echo $db->stderr();
        return false;
    }
    
    HTML_statzone::showTeamPlayers($rows, $option, $teamName);
}

function showPublishedTeamPlayerStats($option)
{
    $teamName = JRequest::getVar('teamName', 0);
    $id = JRequest::getVar('id', 0);
    $db =& JFactory::getDBO();
	
	$seasonPlayersQuery = "select distinct playerid, firstname, surname from jos_statzone_players, ((select playerid 
			from jos_statzone_battingscores batscore
			where batscore.teamid = " . $id . " and batscore.scorecardid in ( select id
							from jos_statzone_scorecards
							where ((substring(season,-9,4) = year(sysdate())) or (substring(season,-4,4) = year(sysdate())))
							and (teamidhome = " . $id . " or teamidopponent = " . $id . ")
							and published = 1
						      ) 
			)
			union
			(
			select playerid 
			from jos_statzone_bowlingscores bowlscore
			where bowlscore.teamid = " . $id . " and bowlscore.scorecardid in ( select id
							from jos_statzone_scorecards
							where ((substring(season,-9,4) = year(sysdate())) or (substring(season,-4,4) = year(sysdate())))
							and (teamidhome = " . $id . " or teamidopponent = " . $id . ")
							and published = 1
						      )
			) ) as season_players
where playerid = id order by surname";
	
	$db->setQuery( $seasonPlayersQuery );
    $seasonPlayersList = $db->loadObjectList();
    if ($db->getErrorNum())
    {
        echo $db->stderr();
        return false;
    }
	
    $query = "SELECT pt.id playerid, batting_playerid,bowl_playerid, pt.contentid,pt.firstname, pt.surname, batting.totalinnings, batting.totalruns, batting.totaloutinnings, batting.fifties, batting.hundreds, batting.highscore,
bowl_totalinnings, bowl_totovers, bowl_totwickets, bowl_totruns, bowl_totmaidens
from jos_statzone_players pt
left join 
(
SELECT p.id batting_playerid,p.contentid,p.firstname,p.surname, 

(SELECT COUNT(*) AS totalinnings
         FROM jos_statzone_battingscores AS bs 
          INNER JOIN jos_statzone_players ptotalinnings ON ptotalinnings.id = bs.playerid
          INNER JOIN jos_statzone_scorecards sc ON sc.id = bs.scorecardid
          WHERE sc.published = 1 and bs.teamid = " . $id . " and sc.innings = 1
          and ptotalinnings.id = p.id) as totalinnings ,
(SELECT sum(runs) AS totalruns
         FROM jos_statzone_battingscores AS bs 
          INNER JOIN jos_statzone_players ptotalruns ON ptotalruns.id = bs.playerid
          INNER JOIN jos_statzone_scorecards sc ON sc.id = bs.scorecardid
          WHERE sc.published = 1 and bs.teamid = " . $id . " and sc.innings = 1
          and ptotalruns.id = p.id) as totalruns ,
(SELECT COUNT(*) AS totaloutinnings
         FROM jos_statzone_battingscores AS bs 
          INNER JOIN jos_statzone_players ptotaloutinnings ON ptotaloutinnings.id = bs.playerid
          INNER JOIN jos_statzone_scorecards sc ON sc.id = bs.scorecardid
          WHERE sc.published = 1 and bs.notout = 0 and bs.teamid = " . $id . " and sc.innings = 1
          and ptotaloutinnings.id = p.id) as totaloutinnings ,

(SELECT COUNT(*) AS fifties
         FROM jos_statzone_battingscores AS bs 
          INNER JOIN jos_statzone_players pfifties ON pfifties.id = bs.playerid
          INNER JOIN jos_statzone_scorecards sc ON sc.id = bs.scorecardid
          WHERE sc.published = 1 and bs.runs >= 50 and bs.teamid = " . $id . " and sc.innings = 1
          and pfifties.id = p.id) as fifties , 

(SELECT COUNT(*) AS hundreds
         FROM jos_statzone_battingscores AS bs 
          INNER JOIN jos_statzone_players phundreds ON phundreds.id = bs.playerid
          INNER JOIN jos_statzone_scorecards sc ON sc.id = bs.scorecardid
          WHERE sc.published = 1 and bs.runs >= 100  and bs.teamid = " . $id . " and sc.innings = 1
          and phundreds.id = p.id) as hundreds ,

(SELECT MAX(bs.runs) AS highscore
         FROM jos_statzone_battingscores AS bs 
          INNER JOIN jos_statzone_players phighscore ON phighscore.id = bs.playerid
          INNER JOIN jos_statzone_scorecards sc ON sc.id = bs.scorecardid
          WHERE sc.published = 1  and bs.teamid = " . $id . " and sc.innings = 1
          and phighscore.id = p.id) as highscore

         FROM jos_statzone_battingscores AS bs 
          INNER JOIN jos_statzone_players p ON p.id = bs.playerid
          INNER JOIN jos_statzone_scorecards sc ON sc.id = bs.scorecardid
          WHERE sc.published = 1 and bs.teamid = " . $id . " and sc.innings = 1
group by p.id,p.contentid, p.firstname, p.surname
order by p.surname
) as batting on pt.id = batting.batting_playerid
left join (

SELECT bs.playerid bowl_playerid, COUNT(*) AS bowl_totalinnings , SUM(bs.overs) AS bowl_totovers,
 SUM(bs.wickets) As bowl_totwickets, SUM(bs.runs) As bowl_totruns, SUM(bs.maidens) As bowl_totmaidens 
FROM jos_statzone_bowlingscores AS bs 
INNER JOIN jos_statzone_scorecards sc ON sc.id = bs.scorecardid
WHERE sc.published = 1 and bs.teamid = " . $id . " and sc.innings = 1
GROUP BY bs.playerid
) as bowling on  pt.id = bowling.bowl_playerid
where (batting_playerid is not null or bowling.bowl_playerid is not null)
and pt.id in ( select distinct playerid from ((select playerid 
			from jos_statzone_battingscores batscore
			where batscore.scorecardid in ( select id
							from jos_statzone_scorecards
							where ((substring(season,-9,4) = year(sysdate())) or (substring(season,-4,4) = year(sysdate())))
							and (teamidhome = " . $id . " or teamidopponent = " . $id . ")
							and published = 1
						      ) 
			)
			union
			(
			select playerid 
			from jos_statzone_bowlingscores bowlscore
			where bowlscore.scorecardid in ( select id
							from jos_statzone_scorecards
							where ((substring(season,-9,4) = year(sysdate())) or (substring(season,-4,4) = year(sysdate())))
							and (teamidhome = " . $id . " or teamidopponent = " . $id . ")
							and published = 1
						      )
			) ) as season_players
		    )
order by pt.surname
";
    $db->setQuery( $query );
    $rows = $db->loadObjectList();
    if ($db->getErrorNum())
    {
        echo $db->stderr();
        return false;
    }

    $twoDayStatsQuery = "SELECT pt.id playerid, batting_playerid,bowl_playerid, pt.contentid,pt.firstname, pt.surname, batting.totalinnings, batting.totalruns, batting.totaloutinnings, batting.fifties, batting.hundreds, batting.highscore,
bowl_totalinnings, bowl_totovers, bowl_totwickets, bowl_totruns, bowl_totmaidens
from jos_statzone_players pt
left join 
(
SELECT p.id batting_playerid,p.contentid,p.firstname,p.surname, 

(SELECT COUNT(*) AS totalinnings
         FROM jos_statzone_battingscores AS bs 
          INNER JOIN jos_statzone_players ptotalinnings ON ptotalinnings.id = bs.playerid
          INNER JOIN jos_statzone_scorecards sc ON sc.id = bs.scorecardid
          WHERE sc.published = 1 and bs.teamid = " . $id . " and sc.innings = 2
          and ptotalinnings.id = p.id) as totalinnings ,
(SELECT sum(runs) AS totalruns
         FROM jos_statzone_battingscores AS bs 
          INNER JOIN jos_statzone_players ptotalruns ON ptotalruns.id = bs.playerid
          INNER JOIN jos_statzone_scorecards sc ON sc.id = bs.scorecardid
          WHERE sc.published = 1 and bs.teamid = " . $id . " and sc.innings = 2
          and ptotalruns.id = p.id) as totalruns ,
(SELECT COUNT(*) AS totaloutinnings
         FROM jos_statzone_battingscores AS bs 
          INNER JOIN jos_statzone_players ptotaloutinnings ON ptotaloutinnings.id = bs.playerid
          INNER JOIN jos_statzone_scorecards sc ON sc.id = bs.scorecardid
          WHERE sc.published = 1 and bs.notout = 0 and bs.teamid = " . $id . " and sc.innings = 2
          and ptotaloutinnings.id = p.id) as totaloutinnings ,

(SELECT COUNT(*) AS fifties
         FROM jos_statzone_battingscores AS bs 
          INNER JOIN jos_statzone_players pfifties ON pfifties.id = bs.playerid
          INNER JOIN jos_statzone_scorecards sc ON sc.id = bs.scorecardid
          WHERE sc.published = 1 and bs.runs >= 50 and bs.teamid = " . $id . " and sc.innings = 2
          and pfifties.id = p.id) as fifties , 

(SELECT COUNT(*) AS hundreds
         FROM jos_statzone_battingscores AS bs 
          INNER JOIN jos_statzone_players phundreds ON phundreds.id = bs.playerid
          INNER JOIN jos_statzone_scorecards sc ON sc.id = bs.scorecardid
          WHERE sc.published = 1 and bs.runs >= 100  and bs.teamid = " . $id . " and sc.innings = 2
          and phundreds.id = p.id) as hundreds ,

(SELECT MAX(bs.runs) AS highscore
         FROM jos_statzone_battingscores AS bs 
          INNER JOIN jos_statzone_players phighscore ON phighscore.id = bs.playerid
          INNER JOIN jos_statzone_scorecards sc ON sc.id = bs.scorecardid
          WHERE sc.published = 1  and bs.teamid = " . $id . " and sc.innings = 2
          and phighscore.id = p.id) as highscore

         FROM jos_statzone_battingscores AS bs 
          INNER JOIN jos_statzone_players p ON p.id = bs.playerid
          INNER JOIN jos_statzone_scorecards sc ON sc.id = bs.scorecardid
          WHERE sc.published = 1 and bs.teamid = " . $id . " and sc.innings = 2
group by p.id,p.contentid, p.firstname, p.surname
order by p.surname
) as batting on pt.id = batting.batting_playerid
left join (

SELECT bs.playerid bowl_playerid, COUNT(*) AS bowl_totalinnings , SUM(bs.overs) AS bowl_totovers,
 SUM(bs.wickets) As bowl_totwickets, SUM(bs.runs) As bowl_totruns, SUM(bs.maidens) As bowl_totmaidens 
FROM jos_statzone_bowlingscores AS bs 
INNER JOIN jos_statzone_scorecards sc ON sc.id = bs.scorecardid
WHERE sc.published = 1 and bs.teamid = " . $id . " and sc.innings = 2
GROUP BY bs.playerid
) as bowling on  pt.id = bowling.bowl_playerid
where (batting_playerid is not null or bowling.bowl_playerid is not null)
and pt.id in ( select distinct playerid from ((select playerid 
			from jos_statzone_battingscores batscore
			where batscore.scorecardid in ( select id
							from jos_statzone_scorecards
							where ((substring(season,-9,4) = year(sysdate())) or (substring(season,-4,4) = year(sysdate())))
							and (teamidhome = " . $id . " or teamidopponent = " . $id . ")
							and published = 1
						      ) 
			)
			union
			(
			select playerid 
			from jos_statzone_bowlingscores bowlscore
			where bowlscore.scorecardid in ( select id
							from jos_statzone_scorecards
							where ((substring(season,-9,4) = year(sysdate())) or (substring(season,-4,4) = year(sysdate())))
							and (teamidhome = " . $id . " or teamidopponent = " . $id . ")
							and published = 1
						      )
			) ) as season_players
		    )
order by pt.surname
";
    $db->setQuery( $twoDayStatsQuery );
    $twoDayStats = $db->loadObjectList();
    if ($db->getErrorNum())
    {
        echo $db->stderr();
        return false;
    }
	
	$oneDayPlayers = array();
	foreach($rows as $stat){
		$oneDayPlayers[$stat->playerid] = $stat;
	}
	
	$twoDayPlayers = array();
	foreach($twoDayStats as $stat){
		$twoDayPlayers[$stat->playerid] = $stat;
	}
    
    HTML_statzone::showTeamPlayerStats($oneDayPlayers ,$twoDayPlayers, $seasonPlayersList, $option, $teamName);
}

function showPublishedPlayer($option)
{
$id = JRequest::getVar('id', 0);
$db =& JFactory::getDBO();
$query = "SELECT * FROM #__statzone_players WHERE
id = ". $id;
$db->setQuery( $query );
$rows = $db->loadObjectList();
if ($db->getErrorNum())
{
echo $db->stderr();
return false;
}
	HTML_statzone::showPlayer($rows, $option);
}

function showPublishedTeamGames($option)
{
$id = JRequest::getVar('id', 0);
$teamName = JRequest::getVar('teamName', 0);
$db =& JFactory::getDBO();

$oneDayGamesQuery = "
select sc.id as scorecardId,firstinndate, secondinndate,sc.firstinnscore_home,sc.secondinnscore_home, 
 sc.firstinnscore_opponent,sc.secondinnscore_opponent, away_team.name opponent,  
 division,winning_team.name winningteam, result, resultdetails
from #__statzone_scorecards sc 
inner join #__statzone_teams away_team on sc.teamidopponent = away_team.id
inner join #__statzone_teams home_team on sc.teamidhome = home_team.id
left join #__statzone_teams winning_team on sc.winningteamid = winning_team.id
where teamidhome = " . $id . "
and (secondinndate is null or secondinndate = '0000-00-00 00:00:00')
order by firstinndate desc, secondinndate desc
";

$twoDayGamesQuery = "
select sc.id as scorecardId,firstinndate, secondinndate,sc.firstinnscore_home,sc.secondinnscore_home, 
 sc.firstinnscore_opponent,sc.secondinnscore_opponent, away_team.name opponent,  
 division,winning_team.name winningteam, result, resultdetails
from #__statzone_scorecards sc 
inner join #__statzone_teams away_team on sc.teamidopponent = away_team.id
inner join #__statzone_teams home_team on sc.teamidhome = home_team.id
left join #__statzone_teams winning_team on sc.winningteamid = winning_team.id
where teamidhome = " . $id . "
and (secondinndate is not null and secondinndate <> '0000-00-00 00:00:00')
order by firstinndate desc, secondinndate desc
";
$db->setQuery( $oneDayGamesQuery );
$oneDayGamesRows = $db->loadObjectList();

$db->setQuery( $twoDayGamesQuery );
$twoDayGamesRows = $db->loadObjectList();
if ($db->getErrorNum())
{
echo $db->stderr();
return false;
}
	HTML_statzone::showTeamGames($oneDayGamesRows, $twoDayGamesRows, $option, $teamName);
}

function showPublishedTeamStats($option)
{
$id = JRequest::getVar('id', 0);
$db =& JFactory::getDBO();
$query = "select id,clubid,name 
from #__statzone_teams teams
where clubid =". $id;
$db->setQuery( $query );
$rows = $db->loadObjectList();
if ($db->getErrorNum())
{
echo $db->stderr();
return false;
}
	HTML_statzone::showTeamStats($rows, $option);
}

    function getStatzTable(&$statzrows)
    {
        $statstable = '
                <table class="contentpane">
                    <thead>
                    <tr>
                        <th class="sectiontableheader" width="20" colspan="1">
                            
                        </th>
                        <th class="sectiontableheader" width="10" colspan="2">
                            Batting
                        </th>
                        <th class="sectiontableheader" width="10" colspan="8">
                            Bowling
                        </th>
                    </tr>
                    <tr>
                        <th class="sectiontableheader" width="20" colspan="1">
                            
                        </th>
                        <th class="sectiontableheader" width="10" colspan="1">
                            1st
                        </th>
                        <th class="sectiontableheader" width="10" colspan="1">
                            2nd
                        </th>
                        <th class="sectiontableheader" width="10" colspan="4">
                            1st
                        </th>
                        <th class="sectiontableheader" width="10" colspan="4">
                            2nd
                        </th>
                    </tr>
                    <tr>
                        <th class="sectiontableheader" width="10">
                            Players
                        </th>
                        <th class="sectiontableheader" width="10">
                            Runs
                        </th>
                         <th class="sectiontableheader" width="10">
                           Runs
                        </th>
                        
                        <th class="sectiontableheader" width="10">
                           O
                        </th>
                        <th class="sectiontableheader" width="10">
                           M
                        </th>
                        <th class="sectiontableheader" width="10">
                            R
                        </th>
                        <th class="sectiontableheader" width="10">
                            W
                        </th>
                        
                        <th class="sectiontableheader" width="10">
                            O
                        </th>
                        <th class="sectiontableheader" width="10">
                            M
                        </th>
                        <th class="sectiontableheader"  width="10">
                            R
                        </th>
                        <th class="sectiontableheader" width="10">
                            W
                        </th>
                    </tr>
                    </thead>
                    <tbody>
                        '.$statzrows.'
                    </tbody>
                </table><br>';
        
        return $statstable;
                
    }
?>